/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 * det her skal virke
 */

package DBlayer;
import Modellayer.*;
import java.sql.*;
import java.util.ArrayList;
/**
 *
 * @author Søren
 */
public class DBInvoice implements IFDBInvoice{
    private Connection con;
    /** Creates a new instance of DBInvoice */
    public DBInvoice() {
      con = DBConnection.getInstance().getDBcon();
    }



  //Implements the methods from the interface
    // get all invoices
    public ArrayList<Invoice> getAllInvoices(boolean retrieveAssociation)
    {
        return miscWhere("" ,retrieveAssociation);
    }

    public Invoice findInvoice(int ID, boolean retrieveAssociation)
    {   String wClause = "  invoiceNo = '" + ID + "'";
        return singleWhere(wClause, retrieveAssociation);
    }

    public int insertInvoice(Invoice invoice)
    { 

       int rc = -1;
	   String query="INSERT INTO persiInvoice(invoiceNo, salesOrderID, paymentDate, status)  VALUES('"+
	  		invoice.getInvoiceNo()  + "','"  +
                        invoice.getOrder().getSalesOrderID() + "','" +
                        invoice.getPaymentDate()  + "','"  +
						invoice.getStatus() + "')";



       System.out.println("insert : " + query);
       try{ // insert new employee +  dependent
          con.setAutoCommit(false);
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
          con.commit();
          con.setAutoCommit(true);
       }//end try
       catch(SQLException ex){
          System.out.println("Invoice ikke oprettet");
          System.out.println("Insert exception in persiInvoice db: "+ex);
	  try{
             rc=-1;
             con.rollback();
             con.setAutoCommit(true);
          }
          catch(Exception ex1)
          {
             System.out.println("Error rolling back"+ ex1);
          }
       }
       return(rc);
    }

	public int updateInvoice(Invoice invoice)
	{
		Invoice invoiceObj  = invoice;
		int rc=-1;

		String query="UPDATE persiInvoice SET "+
		 	  "invoiceID ='"+ invoiceObj.getInvoiceNo() + "', " +
                          "salesOrderID ='"+ invoiceObj.getOrder().getSalesOrderID() + "', "+
                          "paymentDate ='"+ invoiceObj.getPaymentDate() + "' " +
                          "status ='"+ invoiceObj.getStatus();

                System.out.println("Update query:" + query);
  		try{ // update invoice
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in invoice db: "+ex);
	  	}
		return(rc);
	}

	public void deleteInvoice(int ID)
	{
                int rc = -1;
	  	String query="DELETE FROM persiInvoice WHERE invoiceNo = '" +
				ID + "'";
                System.out.println(query);
	  	try{ // delete from persiInvoice
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in persiInvoice db: "+ex);
   	        }
	}

	//private methods
        //michWere is used whenever we want to select more than one invoice


	private ArrayList<Invoice> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Invoice> list = new ArrayList<Invoice>();

	    String query =  buildQuery(wClause);

            try{ // read the invoice from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);


		while( results.next() ){
	     	 Invoice invoiceObj = new Invoice();
		 invoiceObj = buildInvoice(results);
                 list.add(invoiceObj);
		}//end while
                 stmt.close();
                if(retrieveAssociation)
                 {   //The salesOrder is to be build as well
                     for(Invoice invoiceObj : list){

                        IFDBSalesOrder dbSalesOrderObj = new DBSalesOrder();
                        SalesOrder SalesOrderObj = dbSalesOrderObj.findOrder(invoiceObj.getOrder().getSalesOrderID(), false);
                        System.out.println("OrderList is selected");
                        invoiceObj.setOrder(SalesOrderObj);
                     }
                 }//end if

		}//slut try
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}

	//Singelwhere is used when we only select one invoice
	private Invoice singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Invoice invoiceObj = new Invoice();

	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the persiInvoice from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);

	 		if( results.next() ){
                            invoiceObj = buildInvoice(results);
                            //assocaition is to be build
                            stmt.close();
                            if(retrieveAssociation)
                            {   //The salesOrder is to be build as well

								        IFDBSalesOrder dbSalesOrderObj = new DBSalesOrder();
									    SalesOrder SalesOrderObj = dbSalesOrderObj.findOrder(invoiceObj.getOrder().getSalesOrderID(), false);
										System.out.println("OrderList is selected");
										invoiceObj.setOrder(SalesOrderObj);
                            }

			}
                        else{ //no invoice was found
                            invoiceObj = null;
                        }
		}//end try
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return invoiceObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT invoiceNo, salesOrderID, paymentDate, status FROM persiInvoice";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}
	//method to build an invoice object
	private Invoice buildInvoice(ResultSet results)
      {   Invoice invoiceObj = new Invoice();
          try{ // the columns from the table persiInvoice  are used
                invoiceObj.setInvoiceNo(results.getInt("invoiceNo"));
                invoiceObj.setPaymentDate(results.getString("paymentDate"));
                invoiceObj.setStatus(results.getString("status"));
                invoiceObj.setOrder(new SalesOrder());

          }
         catch(Exception e)
         {
             System.out.println("error in building the invoice object");
         }
         return invoiceObj;
      }
}
